from flask import Blueprint, jsonify, current_app, request
from sqlalchemy import desc

from models import Score, Student, Course, db, Teacher  # 假设 Score, Student, Course 是模型，db 是数据库实例
from sqlalchemy.exc import IntegrityError

api_bp = Blueprint('score', __name__)


# 1. 获取成绩列表
@api_bp.route('/scores', methods=['GET'])
def get_scores():
    try:
        page = request.args.get('page', 1, type=int)
        page_size = request.args.get('page_size', 10, type=int)
        page_size = min(max(page_size, 1), 100)

        # 关键词查询参数
        keyword = request.args.get('kw', '').strip()
        # 课程名查询参数
        course = request.args.get('course', '').strip()
        # 最低分查询参数
        minScore = request.args.get('minScore', None, type=int)
        # 最高分查询参数
        maxScore = request.args.get('maxScore', None, type=int)

        # 构建基础查询 - 链式调用 join() 方法
        query = Score.query \
            .join(Student, Score.student_id == Student.id, isouter=True) \
            .join(Course, Score.course_id == Course.id, isouter=True) \
            .join(Teacher, Course.teacher_id == Teacher.id, isouter=True) \
            .add_columns(
                Score.id,
                Student.id.label('student_id'),
                Student.name.label('student_name'),
                Student.studentId,
                Course.id.label('course_id'),
                Course.name.label('course_name'),
                Course.type.label('course_type'),
                Teacher.name.label('teacher_name'),
                Score.score,
                Score.date,
                Score.remark
            ).order_by(desc(Score.date))

        # 添加关键词查询条件
        if keyword:
            keyword = f'%{keyword}%'  # 模糊查询
            query = query.filter(
                (Student.name.ilike(keyword)) |
                (Student.studentId.ilike(keyword))
            )

        # 添加课程名查询条件
        if course:
            course = f'%{course}%'
            query = query.filter(
                (Course.name.ilike(course))
            )

        # 添加最低分查询条件
        if minScore is not None:
            query = query.filter(
                (Score.score >= minScore)
            )

        # 添加最高分查询条件
        if maxScore is not None:
            query = query.filter(
                (Score.score <= maxScore)
            )

        # 查询总记录数（带筛选条件）
        total_items = query.count()
        # 计算总页数
        total_pages = (total_items + page_size - 1) // page_size
        page = max(1, min(page, total_pages))

        # 执行分页查询
        scores = query.offset((page - 1) * page_size).limit(page_size).all()

        # 组装返回数据
        data = []
        for score in scores:
            data.append({
                "id": score.id,
                "student_id": score.student_id,
                "student_name": score.student_name,
                "studentId": score.studentId,
                "course_id": score.course_id,
                "course_name": score.course_name,
                "course_type": score.course_type,
                "teacher_name": score.teacher_name,
                "score": score.score,
                "date": score.date.strftime("%Y-%m-%d"),
                "remark": score.remark
            })

        return jsonify({
            "message": "获取成绩列表成功",
            "data": data,
            "pagination": {
                "current_page": page,
                "page_size": page_size,
                "total_pages": total_pages,
                "total_items": total_items
            }
        }), 200

    except Exception as e:
        current_app.logger.error(f"获取成绩列表失败: {str(e)}", exc_info=True)
        return jsonify({"message": "获取成绩列表失败"}), 500

# 2. 添加成绩
@api_bp.route('/scores', methods=['POST'])
def add_score():
    try:
        data = request.json
        required_fields = ["student_id", "course_id", "score", "date"]
        for field in required_fields:
            if field not in data:
                return jsonify({"message": f"缺少必要参数: {field}"}), 400

        # 验证学生是否存在
        student = Student.query.get(data["student_id"])
        if not student:
            return jsonify({"message": "学生不存在"}), 400

        # 验证课程是否存在
        course = Course.query.get(data["course_id"])
        if not course:
            return jsonify({"message": "课程不存在"}), 400

        # 验证成绩范围
        if not (0 <= int(data["score"]) <= 100):
            return jsonify({"message": "成绩必须在0-100之间"}), 400

        # 检查是否已存在该学生该课程的成绩
        existing_score = Score.query \
            .filter(Score.student_id == data["student_id"]) \
            .filter(Score.course_id == data["course_id"]) \
            .first()
        if existing_score:
            return jsonify({"message": "该学生该课程已有成绩记录"}), 400

        new_score = Score(
            student_id=data["student_id"],
            course_id=data["course_id"],
            score=data["score"],
            date=data["date"],
            remark=data.get("remark", "")
        )
        db.session.add(new_score)
        db.session.commit()
        return jsonify({
            "message": "成绩添加成功",
            "score_id": new_score.id
        }), 201

    except IntegrityError:
        db.session.rollback()
        current_app.logger.error("数据库操作冲突")
        return jsonify({"message": "添加成绩失败"}), 500
    except Exception as e:
        db.session.rollback()
        current_app.logger.error(f"添加成绩失败: {str(e)}", exc_info=True)
        return jsonify({"message": "添加成绩失败"}), 500


# 3. 更新成绩信息
@api_bp.route('/scores/<int:score_id>', methods=['PUT'])
def update_score(score_id):
    try:
        data = request.json
        score = Score.query.get(score_id)

        if not score:
            return jsonify({"message": "成绩记录不存在"}), 404

        # 验证学生是否存在（如果更新了学生ID）
        if "student_id" in data:
            student = Student.query.get(data["student_id"])
            if not student:
                return jsonify({"message": "学生不存在"}), 400

        # 验证课程是否存在（如果更新了课程ID）
        if "course_id" in data:
            course = Course.query.get(data["course_id"])
            if not course:
                return jsonify({"message": "课程不存在"}), 400

        # 验证成绩范围（如果更新了成绩）
        if "score" in data and not (0 <= data["score"] <= 100):
            return jsonify({"message": "成绩必须在0-100之间"}), 400

        # 更新成绩信息
        if "student_id" in data:
            score.student_id = data["student_id"]
        if "course_id" in data:
            score.course_id = data["course_id"]
        if "score" in data:
            score.score = data["score"]
        if "date" in data:
            score.date = data["date"]
        if "remark" in data:
            score.remark = data["remark"]

        db.session.commit()

        return jsonify({
            "message": "成绩信息更新成功",
            "score_id": score.id
        }), 200

    except Exception as e:
        db.session.rollback()
        current_app.logger.error(f"更新成绩信息失败: {str(e)}", exc_info=True)
        return jsonify({"message": "更新成绩信息失败"}), 500


# 4. 删除成绩
@api_bp.route('/scores/<int:score_id>', methods=['DELETE'])
def delete_score(score_id):
    try:
        score = Score.query.get(score_id)

        if not score:
            return jsonify({"message": "成绩记录不存在"}), 404

        db.session.delete(score)
        db.session.commit()

        return jsonify({"message": "成绩已删除"}), 200

    except Exception as e:
        db.session.rollback()
        current_app.logger.error(f"删除成绩失败: {str(e)}", exc_info=True)
        return jsonify({"message": "删除成绩失败"}), 500
